package com.xbang.attendance.service.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.xbang.attendance.entity.product.Product;
import com.xbang.attendance.entity.stock.InventoryWarningRecord;
import com.xbang.attendance.entity.system.MaintenanceStation;
import com.xbang.attendance.entity.system.Region;
import com.xbang.attendance.entity.system.Warehouse;
import com.xbang.attendance.entity.system.WarehouseProductAgreement;
import com.xbang.attendance.service.WarehouseService;
import com.xbang.attendance.utils.DateUtil;
import com.xbang.attendance.utils.FieldUtil;
import com.xbang.core.dao.support.GenericDaoSupport;
import com.xbang.core.service.impl.BaseServiceImpl;
import com.xbang.core.utils.DateUtils;
import com.xbang.core.utils.SendMailUtils;

import enumeration.InventoryOperation;
import enumeration.Temp;

/**
 * @author huawei
 * @email alonelaval@gmail.com
 */
@Service("warehouseService")
public class WarehouseServiceImpl extends BaseServiceImpl implements WarehouseService {

	@Autowired
	private GenericDaoSupport genericDaoSupport;

	@Autowired
	private SendMailUtils sendMailUtils;

	@Override
	public Warehouse getWarehouse(Integer id) throws Exception {
		return genericDaoSupport.get(Warehouse.class, id);
	}

	@Override
	public Warehouse getWarehouseByCode(String code) throws Exception {
		StringBuilder sbBuilder = new StringBuilder("FROM Warehouse warehouse WHERE warehouse.code = ?");
		List list = genericDaoSupport.getHibernateTemplate().find(sbBuilder.toString(), code);
		return null != list && !list.isEmpty() ? (Warehouse) list.get(0) : null;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.xbang.attendance.service.WarehouseService#genericExcelFile(java.lang.String, java.lang.String[])
	 */
	@Override
	public String genericExcelFile(String webPath, String[] fields) throws Exception {
		String genericExcelFile = "stock-".concat(DateUtils.format(new Date(), "yyyyMMddhhmmss")).concat(".xls");
		File file = new File(webPath + "//" + genericExcelFile);
		List orderList = getProducts();
		if (!orderList.isEmpty()) {
			HSSFWorkbook workbook = new HSSFWorkbook();
			HSSFSheet sheet = workbook.createSheet("sheet1");
			sheet.setDefaultColumnWidth((short) 20);
			HSSFRow row = sheet.createRow(0);// 创建第一行
			int i = 0;
			for (FieldUtil.Filed field : FieldUtil.getStockFields()) {
				HSSFCell cell = row.createCell(i);
				cell.setCellValue(field.getName());
				i++;
			}

			int rowIndex = 1;
			for (Object object : orderList) {
				row = sheet.createRow(rowIndex++);
				Map m = (Map) object;
				i = 0;
				for (FieldUtil.Filed field : FieldUtil.getStockFields()) {
					HSSFCell cell = row.createCell(i);
					cell = row.createCell(i);
					if (field.getIsDate()) {
						cell.setCellValue(m.get(field.getCode()) != null ? DateUtil.formatString(m.get(field.getCode()).toString(), DateUtil.DATE_FORMAT) : "");
					} else {
						cell.setCellValue(m.get(field.getCode()) != null ? m.get(field.getCode()).toString() : "");
					}
					i++;
				}
			}

			OutputStream os = new FileOutputStream(file);
			workbook.write(os);// 写入
			InputStream in = new FileInputStream(file);
			os.close();
			in.close();
		}
		return genericExcelFile;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @seecom.xbang.attendance.service.WarehouseService#sendWarehouseWarnMail(com.xbang.attendance.entity.stock.
	 * InventoryWarningRecord)
	 */
	public void sendWarehouseWarnMail(InventoryWarningRecord inventoryWarningRecord) throws Exception {

		sendMailUtils.send(inventoryWarningRecord.getWarehouse().getEmail(), inventoryWarningRecord.getWarehouse().getName(), "仓储预警邮件", inventoryWarningRecord
				.getDescription());
	}

	/**
	 * 得到仓储导出的列表
	 * 
	 * @return
	 */
	private List getProducts() {
		StringBuilder sbBuilder = new StringBuilder(" SELECT ");
		sbBuilder.append(" invert.id AS id,").append(" ware.code AS wareCode,").append(" ware.name AS wareName,").append(" pro.product_code AS proCode,")
				.append(" pro.product_name AS proName,").append(" cate.category_code AS cateCode,").append(" cate.category_name AS cateName,").append(
						" invert.amount AS amount,")
				.append(" pro.specification AS specification,")
				// .append(" pro.material AS material,")
				// .append(" pro.measure_unit AS measureUnit,")
				.append(" invert.last_inventory_operation_date AS DATE ").append(" FROM inventory invert ").append(
						" LEFT JOIN warehouse ware  ON (invert.warehouse_id = ware.id)").append(" LEFT JOIN product pro ON (invert.product_id = pro.id)  ")
				.append(" LEFT JOIN   product_category cate ON (cate.id = pro.product_category_id)");
		logger.info(sbBuilder.toString());
		return genericDaoSupport.getJdbcTemplate().queryForList(sbBuilder.toString());
	}

	@Override
	public WarehouseProductAgreement getWarehouseProductAgreement(Warehouse warehouse, Product product) throws Exception {
		StringBuilder sbBuilder = new StringBuilder("FROM WarehouseProductAgreement agreement WHERE agreement.product.id = ?")
				.append(" and agreement.warehouse.id = ?");
		List<WarehouseProductAgreement> lists = genericDaoSupport.getHibernateTemplate().find(sbBuilder.toString(),
				new Object[] { product.getId(), warehouse.getId() });
		return lists != null && !lists.isEmpty() ? lists.get(0) : null;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.xbang.attendance.service.WarehouseService#getMaintenanceStationByCode(java.lang.String)
	 */
	public MaintenanceStation getMaintenanceStationByCode(String ascNumber) throws Exception {
		Map<String, Object> parameters = new HashMap<String, Object>();
		parameters.put("number", ascNumber);
		List lists = searchForList("FROM MaintenanceStation maintenance WHERE 1 = 1 AND maintenance.number = :number", parameters);
		return lists == null || lists.isEmpty() ? null : (MaintenanceStation) lists.get(0);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.xbang.attendance.service.WarehouseService#getRegionByName(java.lang.String)
	 */
	public Region getRegionByName(String name) throws Exception {
		Map<String, Object> parameters = new HashMap<String, Object>();
		parameters.put("name", name);
		List lists = searchForList("FROM Region region WHERE region.name = :name", parameters);
		return lists == null || lists.isEmpty() ? null : (Region) lists.get(0);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.xbang.attendance.service.WarehouseService#getWarehouseByName(java.lang.String)
	 */
	public Warehouse getWarehouseByName(String name) throws Exception {
		Map<String, Object> parameters = new HashMap<String, Object>();
		parameters.put("name", name);
		List lists = searchForList("FROM Warehouse warehouse WHERE warehouse.name = :name", parameters);
		return lists == null || lists.isEmpty() ? null : (Warehouse) lists.get(0);
	}

	@Override
	public List<Warehouse> getWarehousesByRegion(String regionNumber)
			throws Exception {
		Map<String, Object> parameters = new HashMap<String, Object>();
		parameters.put("number", regionNumber);
		List lists = searchForList("FROM Warehouse warehouse WHERE warehouse.region.number = :number", parameters);
		return lists;
	}

	@Override
	public String genericExcelFile(String webPath,Integer warehouseId,Date beginDate,Date endDate)
			throws Exception {
		String genericExcelFile = "stock-".concat(DateUtils.format(new Date(), "yyyyMMddhhmmss")).concat(".xls");
		File file = new File(webPath + "//" + genericExcelFile);
		
		
		
		List orderList = getList(warehouseId, beginDate, endDate);
		
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet("sheet1");
		sheet.setDefaultColumnWidth((short) 20);
		HSSFRow row = sheet.createRow(0);// 创建第一行
		int i = 0;
		for (FieldUtil.Filed field : FieldUtil.getStockFields()) {
			HSSFCell cell = row.createCell(i);
			cell.setCellValue(field.getName());
			i++;
		}
		
		if (!orderList.isEmpty()) {	
			
			Map<String, Temp> temMap = new HashMap<String, Temp>();
			
			for (Object object : orderList) {
				Map<String,Object> m = (Map) object;
				String proCode =  m.get("proCode").toString();
				Temp  temp = null;
				String rep = m.get("replenishmentSum").toString();
				String ture = m.get("tureOutSum").toString();
				String out = m.get("outSum").toString();
				if(temMap.containsKey(proCode)){
					temp = temMap.get(proCode);
					temp.setReplenishmentSum(temp.getReplenishmentSum().add(new BigDecimal(rep)));
					temp.setTureOutSum(temp.getTureOutSum().add(new BigDecimal(ture)));
					temp.setOutSum(temp.getOutSum().add(new BigDecimal(m.get("outSum").toString())));
					temMap.put(proCode, temp);
				}
				else{
					temp = new Temp();
					temp.setReplenishmentSum(temp.getReplenishmentSum().add(new BigDecimal(rep)));
					temp.setTureOutSum(temp.getTureOutSum().add(new BigDecimal(ture)));
					temp.setOutSum(temp.getOutSum().add(new BigDecimal(m.get("outSum").toString())));
					temp.setProName(m.get("proName").toString());
					temp.setProCode(m.get("proCode").toString());
					temMap.put(proCode,temp);
				}
			}
			int rowIndex = 1;
			for(Map.Entry<String, Temp> entry : temMap.entrySet()){
				row = sheet.createRow(rowIndex++);
				HSSFCell cell = row.createCell(rowIndex);
				Temp t = entry.getValue();
				i = 0;
				cell = row.createCell(i++);
				cell.setCellValue(t.getProCode());
				cell =row .createCell(i++);
				cell.setCellValue(t.getProName());
				cell =row .createCell(i++);
				cell.setCellValue(t.getOutSum().doubleValue());
				cell =row .createCell(i++);
				cell.setCellValue(t.getTureOutSum().doubleValue());
				cell =row .createCell(i++);
				cell.setCellValue(t.getReplenishmentSum().doubleValue());
			}
//			row = sheet.createRow(rowIndex++);	
			//for (FieldUtil.Filed field : FieldUtil.getStockFields()) {
//					HSSFCell cell = row.createCell(i);
//					cell = row.createCell(i);
//					if (field.getIsDate()) {
//						cell.setCellValue(m.get(field.getCode()) != null ? DateUtil.formatString(m.get(field.getCode()).toString(), DateUtil.DATE_FORMAT) : "");
//					} else {
//						cell.setCellValue(m.get(field.getCode()) != null ? m.get(field.getCode()).toString() : "");
//					}
//					i++;
//				}
			
//
			OutputStream os = new FileOutputStream(file);
			workbook.write(os);// 写入
			InputStream in = new FileInputStream(file);
			os.close();
			in.close();
		}
		return genericExcelFile;
	}
	@Override
	public String genericExcelFile(String webPath,String po,Integer warehouseId,Date beginDate,Date endDate)
			throws Exception {
		String genericExcelFile = "in-".concat(DateUtils.format(new Date(), "yyyyMMddhhmmss")).concat(".xls");
		File file = new File(webPath + "//" + genericExcelFile);
		// if(!file.exists()){
		// file.createNewFile();
		// }
		Date date = DateUtil.lowerDay(new Date(), 3);

		// System.out.println(DateUtil.formatDate(date));

		List orderList = getLists(po, warehouseId, beginDate, endDate);

		
			HSSFWorkbook workbook = new HSSFWorkbook();
			HSSFSheet sheet = workbook.createSheet("sheet1");
			sheet.setDefaultColumnWidth((short) 20);

			HSSFRow row = sheet.createRow(0);// 创建第一行

			int i = 0;
			for (FieldUtil.Filed field : FieldUtil.getInventoryFields()) {
				HSSFCell cell = row.createCell(i);
				cell.setCellValue(field.getName());
				i++;
			}
			HSSFCell c1 = row.createCell(i);
			 c1.setCellValue("操作类型");
			
			if (!orderList.isEmpty()) {
				int rowIndex = 1;
				for (Object object : orderList) {
					row = sheet.createRow(rowIndex++);
					Map m = (Map) object;
					i = 0;
					for (FieldUtil.Filed field : FieldUtil.getInventoryFields()) {
						HSSFCell cell = row.createCell(i);
						cell = row.createCell(i);
						String tmpKey = field.getCode();
						Object tmpObject = m.get(tmpKey);
						if (tmpObject != null) {
							if (tmpKey.equals("opDate")) {
								cell.setCellValue(DateUtils.format((Date) tmpObject, "yyyy-MM-dd"));
							} else {
								cell.setCellValue(tmpObject.toString());
							}
						} else {
							cell.setCellValue("");
						}
						i++;
					}
					HSSFCell c2 = row.createCell(i);
					 c2.setCellValue(InventoryOperation.valueOf(po).getAliseName());
				}
		}
			OutputStream os = new FileOutputStream(file);
			workbook.write(os);// 写入
			InputStream in = new FileInputStream(file);
			os.close();
			in.close();
		return genericExcelFile;
	}
	
	private List<Map<String, Object>> getList(Integer warehouseId,Date beginDate,Date endDate) throws Exception{
		StringBuilder sBuilder = new StringBuilder("SELECT pro.product_code AS proCode,")
					  .append(" pro.product_name AS proName,")
					  .append(" CASE record.last_Inventory_Operation  WHEN 'OUT'")
					  .append(" THEN SUM(record.amount) ELSE 0 END AS outSum,")
					  .append(" CASE record.last_Inventory_Operation WHEN   'TRANSFERRED'")
					  .append(" THEN SUM(record.amount) ELSE 0 END AS tureOutSum,")
					  .append(" CASE record.last_Inventory_Operation WHEN  'REPLENISHMENT'")
					  .append(" THEN SUM(record.amount) ELSE 0 END AS replenishmentSum")
					  .append(" FROM inventory_record AS record")
					  .append(" LEFT JOIN product AS pro ON (pro.id = record.product_id)")
					  .append(" WHERE record.warehouse_id = ? AND record.operation_Date > ? AND record.operation_Date < ?")
					  .append(" GROUP BY  pro.product_code,record.last_Inventory_Operation");
		logger.info(sBuilder.toString());
		//return genericDaoSupport.queryForList(sBuilder.toString(),warehouseId);
		return genericDaoSupport.getJdbcTemplate().queryForList(sBuilder.toString(),warehouseId,DateUtil.parseDate(DateUtil.getPreviousDay(beginDate), DateUtil.DATETIME_FORMAT),DateUtil.parseDate(DateUtil.getNextDay(endDate), DateUtil.DATETIME_FORMAT));
	}


	    private List<Map<String, Object>> getLists(String op,Integer warehouseId,Date beginDate,Date endDate) throws Exception{
			StringBuilder sBuilder = new StringBuilder("SELECT war.code AS wCode,")
						  .append("  war.name AS wName,")
						  .append("  pro.product_code AS pCode,")
						  .append("  pro.product_name AS pName,")
						  .append("  inr.operation_date AS opDate,")
						  .append(" SUM(inr.amount) AS amount")
						  .append(" FROM inventory_record inr")
						  .append("   LEFT JOIN warehouse war")
						  .append("  ON (war.id = inr.warehouse_id)")
						  .append(" LEFT JOIN product pro")
						  .append("  ON (inr.product_id = pro.id)")
						  .append(" WHERE  inr.warehouse_id = ?  ")
						  .append(" AND  inr.last_inventory_operation = ?")
						  .append("  AND inr.operation_date > ? AND inr.operation_Date < ?")
						  .append("   GROUP BY inr.product_id");
						  
			logger.info(sBuilder.toString());
			//return genericDaoSupport.queryForList(sBuilder.toString(),warehouseId);
			return genericDaoSupport.getJdbcTemplate().queryForList(sBuilder.toString(),warehouseId,op,DateUtil.parseDate(DateUtil.getPreviousDay(beginDate), DateUtil.DATETIME_FORMAT),DateUtil.parseDate(DateUtil.getNextDay(endDate), DateUtil.DATETIME_FORMAT));
		}
		    
	    
	@Override
	public String genericExcelFile(String webPath,String type) throws Exception {
		String genericExcelFile = "convention.xls";
		File file = new File(webPath + "//" + genericExcelFile);
		Map<Integer,List> map = getRegionAndWarehouse();
		HSSFWorkbook workbook = new HSSFWorkbook();
		
	//	int count=0;
		for (Map.Entry<Integer, List> entry : map.entrySet()) {
			List<Map> list = entry.getValue();
			
			HSSFSheet sheet = workbook.createSheet(list.get(0).get("rname").toString());
			List amounts = getAmounts(Integer.valueOf(list.get(0).get("rid").toString()));
			//sheet.protectSheet(password);
			int r = 0; // row index ;
			int c = 0; // cell index;
			HSSFRow row = sheet.createRow(r++);// 创建第一行
			
			HSSFFont cnFont = workbook.createFont();  
			cnFont.setFontHeightInPoints((short) 10);  
			cnFont.setFontName("宋体");
			
			HSSFCellStyle style  =  workbook.createCellStyle();
			   style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			   style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			   style.setBorderTop(HSSFCellStyle.BORDER_NONE);
			   style.setBorderRight(HSSFCellStyle.BORDER_THIN);
			   style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中  
			   style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//上下居中
			  
			   style.setFont(cnFont);
			
			
			Map<Integer, Integer> cellIndexs = new HashMap<Integer, Integer>();
			row.setHeight((short)1000);
			HSSFCellStyle style2  =  workbook.createCellStyle();
			style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			style2.setBorderTop(HSSFCellStyle.BORDER_NONE);
			style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
			style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中  
			style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//上下居中
			
			HSSFCell cell = row.createCell(c++);
			cell.setCellValue("SGM SAP");
			cell.setCellStyle(style2);
			HSSFCell ce5 = row.createCell(c++);
			ce5.setCellValue("新劲产品中文名称");
			ce5.setCellStyle(style2);
			for (Map m : list) {
				int index = c++;
				HSSFCell cell2 = row.createCell(index);
				cellIndexs.put(Integer.valueOf(m.get("wid").toString()),index);
				cell2.setCellValue(m.get("wname").toString());
				cell2.setCellStyle(style2);
			}
			
			
			Map<Integer,Integer> rowIndexs = new HashMap<Integer, Integer>();
			List products = getProducts1();
			for (Object object : products) {
				int index = r++;
				Map tmMap = (Map) object;

				HSSFRow row2 = sheet.createRow(index);//
				HSSFCell c1 = row2.createCell(0);
				c1.setCellStyle(style);
				
				c1.setCellValue(tmMap.get("product_code").toString());	
				HSSFCell c2 = row2.createCell(1);
				c2.setCellValue(tmMap.get("product_name").toString());
				c2.setCellStyle(style);
				rowIndexs.put(Integer.valueOf(tmMap.get("id").toString()),index);
			}
	
			for (Object object : amounts) {
				Map tmMap =(Map)object;
				Integer pid = Integer.valueOf(tmMap.get("product_id").toString());
				Integer wid = Integer.valueOf(tmMap.get("warehouse_id").toString());
	
				HSSFRow tr = sheet.getRow(rowIndexs.get(pid));
				HSSFCell tCell = tr.createCell(cellIndexs.get(wid));
				if(type.equals("real"))
					tCell.setCellValue(tmMap.get("number").toString());
				else {
					tCell.setCellValue(tmMap.get("virtual_number").toString());
				}
				tCell.setCellStyle(style);
				
				
			}
			sheet.autoSizeColumn(( short ) 0 ); // 调整第一列宽度 
	        sheet.autoSizeColumn(( short ) 1 ); // 调整第二列宽度 
		}
		OutputStream os = new FileOutputStream(file);
		workbook.write(os);// 写入
		InputStream in = new FileInputStream(file);
		os.close();
		in.close();
		return genericExcelFile;
	}
	
	private Map getRegionAndWarehouse(){
		StringBuilder sbBuilder = new StringBuilder("SELECT re.id AS rid,re.name" +
				" AS rname,war.id AS wid , war.code AS wcode,war.name AS wname " +
				"FROM warehouse_product_agreement wpa LEFT JOIN " +
				"warehouse war ON wpa.warehouse_id " +
				"= war.id LEFT JOIN region re ON war.region = re.id GROUP BY re.name,war.code");
		logger.info(sbBuilder.toString());
		//return genericDaoSupport.queryForList(sBuilder.toString(),warehouseId);
		List list= genericDaoSupport.getJdbcTemplate().queryForList(sbBuilder.toString());
		Map<Integer,List> map = new HashMap<Integer,List>();
		
		for (Object object : list) {
			Map map2= (Map) object;
			if(map.containsKey(map2.get("rid"))){
				map.get(map2.get("rid")).add(map2);
			}
			else
			{
				List<Map> list2 = new ArrayList<Map>();
				list2.add(map2);
				map.put((Integer) map2.get("rid"),list2);
			}
		}
		return map; 
	}
	private  List<Map<String, Object>> getProducts1(){
		StringBuilder sBuilder = new StringBuilder("SELECT pro.id,pro.product_code,pro.product_name FROM warehouse_product_agreement wpa LEFT JOIN product pro ON wpa.product_id = pro.id GROUP BY pro.product_code ORDER BY pro.product_code");
		return genericDaoSupport.getJdbcTemplate().queryForList(sBuilder.toString());
	}
//	private List<Map<String,Object>> getNumbers(Integer warehouseId){
//		StringBuilder sbBuilder = new StringBuilder("SELECT wpa.number FROM warehouse_product_agreement wpa LEFT JOIN product pro ON wpa.product_id = pro.id WHERE wpa.warehouse_id = ? ORDER BY pro.product_code");
//		return genericDaoSupport.getJdbcTemplate().queryForList(sbBuilder.toString(), warehouseId);
//	}
	private List<Map<String,Object>> getAmounts(Integer region){
		StringBuilder sbBuilder = new StringBuilder("SELECT number,product_id,warehouse_id,virtual_number FROM warehouse_product_agreement wpa")
		.append(" LEFT JOIN product pro    ON wpa.product_id = pro.id")
		.append(" LEFT JOIN warehouse war ON war.id = wpa.warehouse_id")
		.append(" WHERE war.region = ? ")
		.append("GROUP BY wpa.warehouse_id,wpa.product_id ORDER BY warehouse_id,pro.product_code");
		logger.info(sbBuilder.toString());
		return genericDaoSupport.getJdbcTemplate().queryForList(sbBuilder.toString(),region);
	}
}
